package com.kaka.dao;

import com.kaka.entity.User;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class UserDao {
    public List<User> getUsers(){
        List<User> list = new ArrayList<>();
        // 操作数据库的代码
        try {
            // 链接上数据库
            Class.forName("com.mysql.jdbc.Driver");
            // 获取数据库的链接
            Connection conn =  DriverManager.getConnection("jdbc:mysql://localhost:3306/test_maven?useUnicode=true&characterEncoding=utf8",
                    "root", "");
            // 准备sql语句
            String sql = "select id, username, age, birth from users";
            // 预编译sql语句
            PreparedStatement pstmt =  conn.prepareStatement(sql);
            // 执行sql语句
            ResultSet rs =  pstmt.executeQuery();
            // 使用循环的方式从rs中获取数据
            while (rs.next()){
                User user = new User(rs.getString("username"),
                        rs.getInt("age"),
                        rs.getTimestamp("birth"));
                user.setId(rs.getInt("id"));

                // 将user对象放入list集合中
                list.add(user);
            }

        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    public void save(String username, int age, Date birth) throws Exception{
        // 链接上数据库
        Class.forName("com.mysql.jdbc.Driver");
        // 获取数据库的链接
        Connection conn =  DriverManager.getConnection("jdbc:mysql://localhost:3306/test_maven?useUnicode=true&characterEncoding=utf8",
                "root", "");
        // 保存数据
        String saveSql = "insert into users(username,age,birth) values(?,?,?)";

        PreparedStatement pstmt =  conn.prepareStatement(saveSql);

        // 给占位符赋值
        pstmt.setString(1,username);
        pstmt.setInt(2,age);
        pstmt.setObject(3,birth);

        // 执行sql语句
        pstmt.executeUpdate(); // 适合 insert delete update的操作

        conn.close();

    }

    public void delete(String username, int id) throws Exception{
        // 链接上数据库
        Class.forName("com.mysql.jdbc.Driver");
        // 获取数据库的链接
        Connection conn =  DriverManager.getConnection("jdbc:mysql://localhost:3306/test_maven?useUnicode=true&characterEncoding=utf8",
                "root", "");
        // 保存数据
        String saveSql = "delete from users where username = ? and id = ?";

        PreparedStatement pstmt =  conn.prepareStatement(saveSql);

        // 给占位符赋值
        pstmt.setString(1,username);
        pstmt.setInt(2,id);


        // 执行sql语句
        pstmt.executeUpdate(); // 适合 insert delete update的操作

        conn.close();

    }
}